package com.jdbc;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class PetStore {
// 正确格式
public static final String URL= "jdbc:mysql://localhost:3306/pet_store?useSSL=false&serverTimezone=UTC";
//    public static final String URL="jdbc:mysql://localhost:3306/pet_store?serverTimezone=GMT";
    public static final  String USER="root";
    public static final String PASSWORD="wxm20031128";
    //登录并判断身份
    public static int login(String username, String password){
        try (Connection conn=DataSourceUtils.getConnection()){
            //判断是否存在该用户
            String sql1 = "select * from users where username = ? and password = ?";
            PreparedStatement ps1 = conn.prepareStatement(sql1);
            ps1.setString(1,username);
            ps1.setString(2,password);
            ResultSet rs1 = ps1.executeQuery();
            //判断是否是管理员
            String sql2 = "select * from users where username = ? and password = ? and is_admin =true";
            PreparedStatement ps2 = conn.prepareStatement(sql2);
            ps2.setString(1,username);
            ps2.setString(2,password);
            ResultSet rs2 = ps2.executeQuery();

            if (rs2.next()){
                return 2;//管理员判断
            }else if (rs1.next()){
                return 1;//用户是否存在判断
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    //执行sql语句 对宠物表进行增删改操作
    public static void update(String sql,Object... args) {
        //获得命令对象
        try (Connection conn = DataSourceUtils.getConnection()) {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            //设置参数
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            //执行命令
            int rows = preparedStatement.executeUpdate();
            if (rows > 0) {
                System.out.println("操作成功！");
            } else {
                System.out.println("操作失败！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //查看宠物表信息
    public static <T> List<T> query(Class<T> clazz, String sql, Object... args){
        try (Connection conn = DataSourceUtils.getConnection()){
            //获取命令对象
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            //设置参数
            for(int i = 0;i< args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
            //创建List合集
            List<T> list = new ArrayList<>();
            //执行命令
            ResultSet resultSet = preparedStatement.executeQuery();
            //遍历结果
            while(resultSet.next()){
                //创建对象
                T t = clazz.newInstance();
                //通过反射来读取类的所有属性
                Field[] fields = clazz.getDeclaredFields();
                //遍历所有属性，给属性赋值
                for (Field field:fields){
                    //通过属性名获得结果集中的值
                    Object value = resultSet.getObject(field.getName());
                    //修改属性访问权
                    field.setAccessible(true);
                    //通过属性，set(对象，值)给属性赋值
                    field.set(t,value);
                }
                //添加对象到集合
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //用户菜单
    public static void user(){
        boolean xm=true;
        while (xm){
            System.out.println("=========管理员菜单：=========");
            System.out.println("1.按价格排序查看宠物信息");
            System.out.println("2.按年龄排序查看宠物信息");
            System.out.println("3.购买宠物");
            System.out.println("4.退出");
            System.out.println("============================");
            System.out.print("请输入您要进行的业务:");
            Scanner scanner = new Scanner(System.in);
            int n = scanner.nextInt();
            switch (n){
                case 1:
                    List<Pet> list1 = query(Pet.class,"select * from pets order by price");
                    for (Pet p:list1){
                        System.out.println(p);
                    }
                    break;
                case 2:
                    List<Pet> list2 = query(Pet.class,"select * from pets order by age");
                    for (Pet p:list2){
                        System.out.println(p);
                    }
                    break;
                case 3:
                    System.out.println("请输入查询宠物id:");
                    int id3 = scanner.nextInt();
                    System.out.println("宠物原本信息:");
                    List<Pet> list3 = query(Pet.class,"select * from pets where id =?",id3);
                    for (Pet p:list3){
                        System.out.println(p);
                    }
                    System.out.println("请输入购买宠物用户的id：");
                    int owner_id3 = scanner.nextInt();
                    update("update pets set owner_id=? where id=?",owner_id3,id3);
                    update("update users set points=points-10 where id=?",owner_id3);
                    System.out.println("宠物信息更改为:");
                    List<Pet> list4 = query(Pet.class,"select * from pets where id =?",id3);
                    for (Pet p:list3){
                        System.out.println(p);
                    }
                    break;
                case 4:xm=false;
                    break;
                default:
                    System.out.println("输入错误！请重新输入！");
            }
        }
    }

    //管理员菜单
    public static void admin(){
        boolean xm=true;
        while(xm){
            System.out.println("=========管理员菜单：=========");
            System.out.println("1.添加宠物信息");
            System.out.println("2.删除宠物信息");
            System.out.println("3.修改宠物信息");
            System.out.println("4.查看宠物信息");
            System.out.println("5.退出");
            System.out.println("============================");
            System.out.print("请输入您要进行的业务:");
            Scanner scanner = new Scanner(System.in);
            int n = scanner.nextInt();
            switch (n){
                case 1:
                    System.out.print("请输入宠物主人id：");
                    int owner_id1 = scanner.nextInt();
                    System.out.print("请输入宠物昵称：");
                    String nickname1 = scanner.next();
                    System.out.print("请输入宠物类型：");
                    String pet_type1 = scanner.next();
                    System.out.print("请输入宠物年龄：");
                    int age1 = scanner.nextInt();
                    System.out.print("请输入性别：");
                    String gender1 = scanner.next();
                    System.out.print("请输入宠物价格：");
                    BigDecimal price1 = scanner.nextBigDecimal();
                    update("insert into pets (owner_id,nickname, pet_type, age, gender,price) VALUES(?,?,?,?,?,?)",
                            owner_id1,nickname1,pet_type1,age1,gender1,price1);
                    break;
                case 2:
                    System.out.println("请输入需要删除宠物的id：");
                    int id2 = scanner.nextInt();
                    update("delete from pets where id = ?",id2);
                    break;
                case 3:
                    System.out.println("请输入需要修改信息的宠物的id：");
                    int id3 = scanner.nextInt();
                    System.out.print("请输入宠物主人id：");
                    int owner_id3 = scanner.nextInt();
                    System.out.print("请输入宠物昵称：");
                    String nickname3 = scanner.next();
                    System.out.print("请输入宠物类型：");
                    String pet_type3 = scanner.next();
                    System.out.print("请输入宠物年龄：");
                    int age3 = scanner.nextInt();
                    System.out.print("请输入性别：");
                    String gender3 = scanner.next();
                    System.out.print("请输入宠物价格：");
                    BigDecimal price3 = scanner.nextBigDecimal();
                    update("update pets set owner_id=?, nickname=?, pet_type=?, age=?, gender=?, price=? where id=?",
                            owner_id3,nickname3,pet_type3,age3,gender3,price3,id3);
                    break;
                case 4:
                    List<Pet> list1 = query(Pet.class,"select * from pets");
                    for (Pet p:list1){
                        System.out.println(p);
                    }
                    break;
                case 5:xm=false;
                    break;
                default:
                    System.out.println("输入错误！请重新输入！");
            }
        }

    }

    //main
    public static void main(String[] args) {
        for (int i = 0 ;i < 3 ;i++){
            System.out.println("欢迎来到宠物商店！");
            Scanner scanner = new Scanner(System.in);
            System.out.print("请输入您的账号名：");
            String username = scanner.next();
            System.out.print("请输入您的密码：");
            String password = scanner.next();
            if (login(username,password)==1){//普通用户身份
                System.out.println("欢迎用户！");
                user();
                System.out.println("欢迎下次光临！");
                break;
            }else if (login(username,password)==2){//管理员身份
                System.out.println("欢迎管理员！");
                admin();
                System.out.println("欢迎下次使用！");
                break;
            }else {
                System.out.println("输入错误！您还有"+(2-i)+"次机会！");
            }
        }

    }

}